# -*- coding: utf-8 -*-
import sqlite3

CREATE_USER_TABLE = '''CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
password TEXT NOT NULL,
level INTEGER NOT NULL,
EXP INTEGER NOT NULL,
HP INTEGER NOT NULL,
ammunition INTEGER NOT NULL)
'''

class DBAccess(object):
    """Access users database."""
    def __init__(self):
        self.con = sqlite3.connect('server.db')
        self.cur = self.con.cursor()

        self.cur.execute("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='users';")
        exist = self.cur.fetchone()[0]
        if exist == 0:
            # Create table
            self.cur.execute(CREATE_USER_TABLE)

            test_users = [
                ('netease1', '123', 1, 0, 100, 9),
                ('netease2', '123', 1, 0, 100, 9),
                ('netease3', '123', 1, 0, 100, 9)
            ]
            self.cur.executemany("INSERT INTO users VALUES (NULL,?,?,?,?,?,?)", test_users)

            # Save (commit) the changes
            self.con.commit()

    def create_user(self, name, password):
        if self.verify(name):  # if user already exists
            return False
        self.cur.execute("INSERT INTO users VALUES (NULL,'%s','%s',1,0,100,9)" % (name, password))
        self.con.commit()
        return True

    def verify(self, name, password=None):
        self.cur.execute("SELECT password FROM users WHERE name='%s'" % name)
        match = self.cur.fetchone()
        if not match:
            return False  # user does not exist
        elif password and password != match[0]:
            return False  # wrong password
        else:
            return True

    def delete_user(self, name, password):
        if not self.verify(name, password):
            return False
        self.cur.execute("DELETE FROM users WHERE name = '%s' AND password = '%s';" % (name, password))
        self.con.commit()
        return True

    def update(self, name, password, item, value):
        if self.verify(name, password):
            try:
                self.cur.execute("UPDATE users SET %s='%s' WHERE name='%s'" % (item, value, name))
                self.con.commit()
                return True
            except Exception as e:
                print(e)
                return False
        else:
            return False

    def show(self):
        self.cur.execute("SELECT * from users")
        print(self.cur.fetchall())

    def get_attr(self, name, password):
        if self.verify(name, password):
            self.cur.execute("PRAGMA table_info(users)")
            table_info = self.cur.fetchall()
            title = [item[1] for item in table_info]
            self.cur.execute("SELECT * FROM users WHERE name='%s'" % name)
            attr = self.cur.fetchall()[0]
            ret = dict(zip(title, attr))
            return ret
        else:
            return None

    def __del__(self):
        # close the connection
        self.con.close()


if __name__ == "__main__":
    db = DBAccess()
    db.show()

    assert db.verify('xxx') == False
    assert db.verify('netease1') and db.verify('netease2') and db.verify('netease3')
    assert db.verify('netease1', '123') and db.verify('netease2', '123') and db.verify('netease3', '123')
    assert db.verify('netease1', 'wrong password') == False
    print('---------- get attr dict ----------')
    print(db.get_attr('netease1', '123'))

    # 创建新用户时，若用户名已经存在, 则失败
    assert db.create_user('netease1', '123')==False
    assert db.create_user('test1', 'test11')
    assert db.create_user('test2', 'test22')
    assert db.update('test1', 'test11', 'password', 'new11')
    print('---------- just after update password ----------')
    db.show()
    assert db.update('test1', 'wrong password', 'password', 'new password') == False
    assert db.update('test1', 'new11', 'password', 'test11')
    assert db.delete_user('test1', 'test11')
    assert db.delete_user('test2', 'wrong password') == False
    assert db.delete_user('test2', 'test22')
    assert db.update('netease1', '123', 'password', '321')
    del db
    db = DBAccess()
    assert db.update('netease1', '321', 'password', '123')
    assert db.update('netease1', '123', 'xxx', '123') == False
    assert db.update('netease1', '123', 'HP', 1000)
    assert db.update('netease1', '123', 'level', 1000)
    assert db.update('netease1', '123', 'EXP', 222)
    db.show()
